/**
 * 如果有求和，那数据源必须是数字格
 * @param {*} _spread 原Spread对象
 * @param {*} ds 后端的数据源名称，默认ds.data
 */
export function convertSpjs2Gc(_spread, ds = "ds.data") {
    let sheetTabCount = _spread.getSheetTabCount()
    // 创建临时工作簿，后面导出为xlsx
    for (let i = 0; i < sheetTabCount; i++) {
        let temp_spread = new GC.Spread.Sheets.Workbook()
        let _sheetTab = _spread.getSheetTab(i)
        // 在临时工作簿插入普通Sheet
        let temp_sheet = new GC.Spread.Sheets.Worksheet(_sheetTab.name())
        temp_spread.addSheet(i, temp_sheet)
        // template 是我们要转换为Excel模板的对象
        let template = _sheetTab.templateSheet
        let usedRange = template.getUsedRange(GC.Spread.Sheets.UsedRangeType.data)

        for (let r = usedRange.row + usedRange.rowCount - 1; r >= usedRange.row; r--) {
            for (let c = usedRange.col + usedRange.colCount; c >= usedRange.col; c--) {
                setCellAttr(template, temp_sheet, ds, r, c, usedRange)
            }
        }
        temp_spread.export(function (blob) {
            // save blob to a file
            saveAs(blob, _sheetTab.name() + ".xlsx");
        }, function (e) {
        }, {
            fileType: GC.Spread.Sheets.FileType.excel
        });
    }

}

function setCellAttr(template, sheet, ds, r, c, usedRange) {
    setCellStyle(template, sheet, r, c, usedRange)
    setCellBindingPath(template, sheet, ds, r, c)
}

function setCellStyle(template, sheet, r, c, usedRange) {
    let style = template.getActualStyle(r, c)
    sheet.setStyle(r, c, style)
    if (r == usedRange.row) {
        sheet.setColumnWidth(c, template.getColumnWidth(c))
    }
    if (c == usedRange.col) {
        sheet.setRowHeight(r, template.getRowHeight(r))
    }
    // 处理span
    if (r == usedRange.row && c == usedRange.col) {
        let spans = template.getSpans()
        spans.forEach(span => {
            sheet.addSpan(span.row, span.col, span.rowCount, span.colCount)
        })
    }
}

function setCellBindingPath(template, sheet, ds, r, c) {
    let binding = template.getBindingPath(r, c)
    // 未设置
    if (!binding) {
        sheet.setValue(r, c, template.getValue(r, c))
    }

    // 绑定路径
    let path = ""
    switch (binding && binding.type) {
        case "Group":
        case "List":
            // 获取绑定路径
            if (binding.binding) {
                let p = binding.binding.match(/(?<=\[)[^\]\[]*(?=\])/g).join(".")
                path += ds + "." + p
            }
            break;
        case "Summary":
            // 获取绑定路径
            if (binding.binding) {
                let p = binding.binding.match(/(?<=\[)[^\]\[]*(?=\])/g).join(".")
                path += ds + "." + p
            }
            path = "=" + binding.aggregate + "(" + path + ")"
            break
        default:
            break;
    }

    let option = ""
    // Group类型
    let group = ""
    if (binding && binding.type == "Group") {
        group = "G=M"
    } else if (binding && binding.type == "List") {
        group = "G=L"
    }
    option = option + group
    // Expand方向
    let expand = ""
    if (binding && binding.type != "Summary" && binding.spillDirection == "Vertical") {
        expand = "E=V"
    } else if (binding && binding.type != "Summary" && binding.spillDirection == "Horizontal") {
        expand = "E=H"
    }
    option = option ? option + "," + expand : option + expand

    // 上下文
    let context_v = ""
    let context_h = ""
    if (!binding || binding.context?.vertical == "Default") {
        for (let i = c - 1; i >= 0; i--) {
            let span = template.getSpan(r, i)
            let _r = r
            let _c = i
            if (span) {
                _r = span.row
                _c = span.col
            }
            let fp_binding = template.getBindingPath(_r, _c)
            if (fp_binding && (fp_binding.type == "Group" || fp_binding.type == "List") && fp_binding.spillDirection == "Vertical") {
                context_v = GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(_r, _c, 1, 1), 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative)
                break
            }
        }
    } else {
        context_v = binding.context?.horizontal

    }
    if (!binding || binding.context?.horizontal == "Default") {
        for (let i = r - 1; i >= 0; i--) {
            let span = template.getSpan(i, c)
            let _r = i
            let _c = c
            if (span) {
                _r = span.row
                _c = span.col
            }
            let fp_binding = template.getBindingPath(_r, _c)

            if (fp_binding && (fp_binding.type == "Group" || fp_binding.type == "List") && fp_binding.spillDirection == "Horizontal") {
                context_h = GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(_r, _c, 1, 1), 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative)
                break
            }
        }
    } else {
        context_h = binding.context?.vertical

    }
    let context = ""
    if (context_h && context_v) {
        context = context_v + "*" + context_h
    } else {
        context = context_h || context_v
    }
    if (context) {
        context = "C=" + context
        option = option + "," + context
    }
    let value = ""
    option = option.replace(/^,|,$/g, "")
    if (option) {
        value = `{{${path}(${option})}}`
    } else {
        value = `{{${path}}}`
    }
    let cellText = template.getValue(r, c) || ""
    if (value == "{{}}") {
        sheet.setValue(r, c, cellText)
    } else {

        sheet.setValue(r, c, cellText + value)
    }
    setFormulas(template, sheet, r, c, context)

}

function setFormulas(template, sheet, r, c, context) {
    let formula = template.getFormula(r, c)
    if (!formula) {
        return
    }
    let reg = /(?<=\()[^\]\[]*(?=\))/g
    let arg = formula.match(reg)
    if (arg) {
        arg = arg[0]
    }
    let str = ""
    if (arg) {
        let range = GC.Spread.Sheets.CalcEngine.formulaToRange(template, arg)
        for (let r = range.row; r < range.row + range.rowCount; r++) {
            for (let c = range.col; c < range.col + range.colCount; c++) {
                str += GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(r, c, 1, 1), 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative) + ","
            }
        }
    }
    str = str.replace(/,$/g, "")
    formula = formula.replace(reg, str)
    if (context) {
        formula += "(" + context + ")"
    }
    sheet.setValue(r, c, "{{==" + formula + "}}")
}